package com.xing.dao.user;

import com.mysql.cj.util.StringUtils;
import com.xing.dao.BaseDao;
import com.xing.pojo.User;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
//用户Dao实现类
public class UserDaoImpl implements UserDao{
    //得到登录用户
    @Override
    public User getLoginUser(String userCode,String userPassword) throws SQLException, ClassNotFoundException {
        ResultSet execute = null;
        User user = null;
        String sql = "select * from smbms_user where userCode=? and userPassword=?";
        Object[] objects = {userCode, userPassword};
        BaseDao baseDao = new BaseDao();
        execute = baseDao.execute(sql, objects);
        if (execute.next()){
            user = new User();
            user.setId(execute.getInt("id"));
            user.setUserCode(execute.getString("userCode"));
            user.setUserName(execute.getString("userName"));
            user.setUserPassword(execute.getString("userPassword"));
            user.setGender(execute.getInt("gender"));
            user.setBirthday(execute.getDate("birthday"));
            user.setPhone(execute.getString("phone"));
            user.setAddress(execute.getString("address"));
            user.setUserRole(execute.getInt("userRole"));
            user.setCreatedBy(execute.getInt("createdBy"));
            user.setCreationDate(execute.getTimestamp("creationDate"));
            user.setModifyBy(execute.getInt("modifyBy"));
            user.setModifyDate(execute.getTimestamp("modifyDate"));//获取值

        // 关闭资源，连接有可能还要使用，不关闭
        baseDao.closeAll();
        }
        return user;
    }
    //修改当前用户密码
    @Override
    public int updatePwd(int id, String userPassword) throws SQLException, ClassNotFoundException {
        User user = null;
        int result = 0;
        String sql = "update smbms_user set userPassword = ? where id = ?";
        Object[] objects={userPassword,id};
        BaseDao baseDao = new BaseDao();
        result = baseDao.update(sql, objects);
        baseDao.closeAll();
        return result;
    }

    @Override
    //根据用户名或者角色查询用户总数
    public int getUserCount(String username, int userRole) throws SQLException, ClassNotFoundException {
        ResultSet resultSet = null;
        int count = 0;
        StringBuffer sql = new StringBuffer();
        sql.append("select count(1) as count from smbms_user u ,smbms_role r where u.userRole = r.id ");
        ArrayList<Object> list = new ArrayList<>();

        if (!StringUtils.isNullOrEmpty(username)){
            sql.append("and u.userName like ? ");
            list.add("%"+username+"%"); //index:0
        }
        if (userRole>0){
            sql.append("and u.userRole = ? ");
            list.add(userRole);//index=1
        }
        //将list转换为数组
        Object[] objects = list.toArray();
        System.out.println("UserDaoImpl->getUserCount:"+sql.toString());//输出完整的SQL语句
        BaseDao baseDao = new BaseDao();
        resultSet = baseDao.execute(sql.toString(), objects);
        if (resultSet.next()){
             count = resultSet.getInt("count");//从结果集中获取最终的数量
            baseDao.closeAll();
        }
        return count;

    }
    //获取用户列表
    @Override
    public List<User> getUserList(String username, int userRole, int currentPageNo, int pageSize) throws SQLException, ClassNotFoundException {
        ArrayList<User> userList = new ArrayList<User>();
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer();
        sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
        ArrayList<Object> list = new ArrayList<>();
        if (!StringUtils.isNullOrEmpty(username)){
            sql.append(" and u.userName like ?");
            list.add("%"+username+"%");
        }
        if (userRole>0){
            sql.append(" and u.userRole = ? ");
            list.add(userRole);//index=1
        }
        //分页，limit
        sql.append(" order by creationDate desc limit ?,?");
        currentPageNo = (currentPageNo-1)*pageSize;
        list.add(currentPageNo);
        list.add(pageSize);
        Object[] objects=list.toArray();
        System.out.println("sql--->"+sql.toString());
        BaseDao baseDao = new BaseDao();
        resultSet = baseDao.execute(sql.toString(), objects);
        while (resultSet.next()){
            User _user = new User();
            _user.setId(resultSet.getInt("id"));
            _user.setUserCode(resultSet.getString("userCode"));
            _user.setUserName(resultSet.getString("userName"));
            _user.setUserPassword(resultSet.getString("userPassword"));
            _user.setGender(resultSet.getInt("gender"));
            _user.setBirthday(resultSet.getDate("birthday"));
            _user.setPhone(resultSet.getString("phone"));
            _user.setUserRole(resultSet.getInt("userRole"));
            _user.setUserRoleName(resultSet.getString("userRoleName"));
            userList.add(_user);
        }
        baseDao.closeAll();
        return userList;
    }

    //通过用户编码获取用户
    @Override
    public User getUserByUserCode(String userCode) throws SQLException, ClassNotFoundException {
        ResultSet rs = null;
        User user = new User();
        String sql = "select * from smbms_user where userCode = ?";
        ArrayList<String> tmp = new ArrayList<>();
        tmp.add(userCode);
        Object[] objects = tmp.toArray();
        BaseDao baseDao1 = new BaseDao();
        rs = baseDao1.execute(sql, objects);
        while (rs.next()) {
            user.setId(rs.getInt("id"));
            user.setUserCode(rs.getString("userCode"));
            user.setUserName(rs.getString("userName"));
            user.setUserName(rs.getString("userPassword"));
            user.setGender(rs.getInt("gender"));
            user.setBirthday(rs.getDate("birthday"));
            user.setPhone(rs.getString("phone"));
            user.setUserRole(rs.getInt("userRole"));
        }
        baseDao1.closeAll();
        return user;
    }

    // 通过用户id查询用户
    @Override
    public User getUserById(int id) throws SQLException, ClassNotFoundException {
        ResultSet rs = null;
        User user = new User();
        String sql = "select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id and u.id = ?";
        Object[] objects = new Object[1];
        objects[0] = id;
        BaseDao baseDao = new BaseDao();
        rs = baseDao.execute(sql, objects);
        while (rs.next()) {
            user.setId(rs.getInt("id"));
            user.setUserCode(rs.getString("userCode"));
            user.setUserName(rs.getString("userName"));
            user.setUserPassword(rs.getString("userPassword"));
            user.setGender(rs.getInt("gender"));
            user.setBirthday(rs.getDate("birthday"));
            user.setPhone(rs.getString("phone"));
            user.setAddress(rs.getString("address"));
            user.setUserRole(rs.getInt("userRole"));
            user.setUserRoleName(rs.getString("userRoleName"));

        }
        baseDao.closeAll();
        return user;
    }

    //添加用户
    @Override
    public int addUser(User user) throws SQLException, ClassNotFoundException {
        int updateRows = 0;
        String sql = "insert into smbms_user (userCode,userName,userPassword,gender," +
                "birthday,phone,address,userRole,createdBy,creationDate,modifyBy,modifyDate) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[] objects = {user.getUserCode(), user.getUserName(), user.getUserPassword(),
                user.getGender(), user.getBirthday(), user.getPhone(),
                user.getAddress(), user.getUserRole(), user.getCreatedBy(),
                user.getCreationDate(), user.getModifyBy(), user.getModifyDate()};
        BaseDao baseDao = new BaseDao();
        updateRows = baseDao.update(sql, objects);
        baseDao.closeAll();
        return updateRows;
    }

    //删除用户
    @Override
    public int delUser(int id) throws SQLException, ClassNotFoundException {
        int updateRows = 0;
        String sql = "delete from smbms_user where id = ?";
        Object[] objects = {id};
        BaseDao baseDao = new BaseDao();
        updateRows = baseDao.update(sql, objects);
        baseDao.closeAll();
        return updateRows;
    }

    //修改用户
    @Override
    public int modifyUser(User user) throws SQLException, ClassNotFoundException {
        int updateRows = 0;
        String sql = "update smbms_user set userCode=?, userName=?, userPassword= ?,gender=?, birthday=?, phone=?," +
                "address=?, userRole=?, modifyBy=?, modifyDate=? where id=?";
        Object[] objects = {user.getUserCode(), user.getUserName(), user.getUserPassword(),user.getGender(), user.getBirthday(),
                user.getPhone(), user.getAddress(), user.getUserRole(), user.getModifyBy(),
                user.getModifyDate(), user.getId()};
        BaseDao baseDao = new BaseDao();
        updateRows = baseDao.update(sql,objects);
        baseDao.closeAll();
        return updateRows;
    }

}
